﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ToppingControl.Models
{
   public class ReportModel
   {
      public class Report_DS
      {
         public string _pTopno { get; set; }
         public string _pPlandt { get; set; }
         public string _pItemno { get; set; }
         public string _pTopcrdcd { get; set; }
         public string _pManufc { get; set; }
         public string _pLength { get; set; }
         public string _pPlants { get; set; }
         public string _pUpTemp { get; set; }
         public string _pUptdat { get; set; }
         public string _pActflag { get; set; }
         public string _pActdt { get; set; }
         public string _pRunflag { get; set; }
         public string _pRundt { get; set; }
         public string _pEmpname { get; set; }
         public string _pShift { get; set; }
         public string _pFinflag { get; set; }
         public string _pFindt { get; set; }
         public string _pTcrdcd { get; set; }
         public string _pTcrdlot { get; set; }
         public string _pTcrdexp { get; set; }
         public string _pTcrdtagl { get; set; }
         public string _pTcrdtagw { get; set; }
         public string _pTcrdw { get; set; }
         public string _pTauthemp { get; set; }
         public string _pTreatxt { get; set; }
         public string _pTshtxt { get; set; }
         public string _pCompcd { get; set; }
         public string _pComplot { get; set; }
         public string _pComppllt { get; set; }
         public string _pCompexp { get; set; }
         public string _pCompw { get; set; }
         public string _pCauthemp { get; set; }
         public string _pCreatxt { get; set; }
         public string _pCshtxt { get; set; }
         public string _pStdform { get; set; }
         public string _pSample { get; set; }
         public string _Skpflag { get; set; }
         public string _skpdt { get; set; }
         public string _skpemp { get; set; }
         public string _skpshtxt { get; set; }
         public string _smpemp { get; set; }
         public string _smpshtxt { get; set; }



         public string _aPrddate { get; set; }
         public string _aRollno { get; set; }
         public string _aLenght { get; set; }
         public string _aWidgth { get; set; }
         public string _aExpired { get; set; }
         public string _a50ds { get; set; }
         public string _a50os { get; set; }
         public string _a50wd { get; set; }
         public string _a100ds { get; set; }
         public string _a100os { get; set; }
         public string _a100wd { get; set; }
         public string _a200ds { get; set; }
         public string _a200os { get; set; }
         public string _a200wd { get; set; }
         public string _a300ds { get; set; }
         public string _a300os { get; set; }
         public string _a300wd { get; set; }
         public string _a400ds { get; set; }
         public string _a400ms { get; set; }
         public string _a400os { get; set; }
         public string _a400wd { get; set; }
         public string _aStdUsed { get; set; }
         public string _aEmpname { get; set; }
         public string _aShift { get; set; }
         public string _aUpdate { get; set; }
         public string _aFinflag { get; set; }
         public string _aFindat { get; set; }

         public string _allcate { get; set; }

      }


      public List<Report_DS> GET_ACT_FORTOP(string top = "", string prdF="", string prdT="")
      {
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine("  select " + top);
         str.AppendLine(" p.[topno] Ptopno , p.plandt Pplandt ");
         str.AppendLine(",p.itemno Pitemno , p.topcrdcd Ptopcrdcd ");
         str.AppendLine(",p.manufc,p.length plen,uptemp nmModif,uptdat dtModif ");
         str.AppendLine(",b.tcrdcd PTcrdcd, p.[Tcrdlot] PTcrdlot, [tcrdexp] pTcrdexp, [Tcrdtagl], [Tcrdtagw] ");
         str.AppendLine(",p.[Plansts], p.[actflag] Pactflag, p.[actdt] Pactdt");
         str.AppendLine(", p.[runflag], p.[Rundat], p.[empname] pEmp, p.[shift], p.[finflag] Pfinflag, p.[findat] Pfindat");
         str.AppendLine(" , p.[Tcrdcd], p.[Tcrdlot], p.[tcrdexp], p.[Tcrdtagl], p.[Tcrdtagw],p.Tcrdw");
         str.AppendLine(" , p.[Comppllt], p.[Compexp], p.[compw], p.[Cauthemp], p.[creatxt], p.[cshtxt],tauthemp,treatxt,Tshtxt");
         str.AppendLine(", p.[Cauthemp], p.[creatxt], p.[cshtxt],p.compcd ,sample ,Complot,Comppllt,Compexp,compw,Cauthemp,creatxt,cshtxt");
         str.AppendLine(", [Skpflag], [skpdt], [skpemp], [skpshtxt], [smpemp], [smpshtxt] ");
         str.AppendLine(",a.* from ");

         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN p ");
         str.AppendLine(" left join " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT a on p.[topno]=a.[topno] and p.plandt=a.plandt ");
         str.AppendLine("and p.itemno=a.itemno and p.topcrdcd=a.topcrdcd  ");
         str.AppendLine(" left join " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM b on  p.[topcrdcd]=b.topcrdcd and p.Tcrdcd=b.irccrdcd ");

         string where = "";
         
          
          if ((StrUtil.IsNotEmpty(prdF) && StrUtil.IsNotEmpty(prdT)))
         {
             where = " where p.plandt between convert(datetime,@dtF,120) and convert(datetime,@dtT,120) ";
         }
         else if (StrUtil.IsNotEmpty(prdF))
         {
             where = " where p.plandt=convert(datetime,@dtF,120) ";
         }




         SqlCommand cmd = new SqlCommand(str.ToString()+ where + " ORDER BY P.TOPNO,P.PLANDT,P.ITEMNO ", cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;

         if (StrUtil.IsNotEmpty(prdF))
         {
             cmd.Parameters.Add("@dtF", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdF, WinNemoLib.DateUtil.eLanguage.EN);
         }

         if (StrUtil.IsNotEmpty(prdT))
         {
             cmd.Parameters.Add("@dtT", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdT, WinNemoLib.DateUtil.eLanguage.EN);
         }
         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<Report_DS> list = new List<Report_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            Report_DS model = new Report_DS();
            model._Skpflag = items["Skpflag"].ToString();
            model._skpemp = items["skpemp"].ToString();
            model._skpdt = DateUtil.ToDisplayDateTime(items["skpdt"], DateUtil.eLanguage.EN);
            model._skpshtxt = items["skpshtxt"].ToString();
            model._smpemp = items["smpemp"].ToString();
            model._smpshtxt = items["smpshtxt"].ToString();
            model._pTopno = items["Ptopno"].ToString();
            model._pItemno = items["Pitemno"].ToString();
            model._pTopcrdcd = items["Ptopcrdcd"].ToString();
            model._pManufc = items["manufc"].ToString();
            model._pPlants = items["Plansts"].ToString();
            model._pPlandt = DateUtil.ToDisplayDate(items["Pplandt"]);
            model._pLength = items["plen"].ToString();
            model._pShift = items["shift"].ToString();
            model._pUptdat = DateUtil.ToDisplayDateTime(items["dtModif"],DateUtil.eLanguage.EN);
            model._pUpTemp = items["nmModif"].ToString();
            model._pTcrdcd = items["PTcrdcd"].ToString();
            model._pCompcd = items["compcd"].ToString();
            model._pEmpname = items["pEmp"].ToString();
            model._pActflag = items["Pactflag"].ToString();
            model._pActdt = DateUtil.ToDisplayDateTime(items["Pactdt"], DateUtil.eLanguage.EN);
            model._pRunflag = items["runflag"].ToString();
            model._pRundt = DateUtil.ToDisplayDateTime(items["Rundat"], DateUtil.eLanguage.EN);

            model._pFinflag = items["Pfinflag"].ToString();
            model._pFindt = DateUtil.ToDisplayDateTime(items["Pfindat"], DateUtil.eLanguage.EN);

            model._pTcrdlot = items["PTcrdlot"].ToString();
            model._pTcrdexp = DateUtil.ToDisplayDate(items["pTcrdexp"], DateUtil.eLanguage.EN);
            model._pTcrdtagl = items["Tcrdtagl"].ToString();
            model._pTcrdtagw = items["Tcrdtagw"].ToString();
            model._pTcrdw = items["Tcrdw"].ToString();
            model._pTauthemp = items["tauthemp"].ToString();
            model._pTreatxt = items["Treatxt"].ToString();
            model._pTshtxt = items["Tshtxt"].ToString();

            model._pSample = items["sample"].ToString();
            model._pCompcd = items["compcd"].ToString();
            model._pComplot = items["Complot"].ToString();
            model._pComppllt = items["Comppllt"].ToString();
            model._pCompexp = DateUtil.ToDisplayDate(items["Compexp"], DateUtil.eLanguage.EN);
            model._pCompw = items["compw"].ToString();
            model._pCauthemp = items["Cauthemp"].ToString();
            model._pCreatxt = items["creatxt"].ToString();
            model._pCshtxt = items["cshtxt"].ToString();       
          
            model._aPrddate = DateUtil.ToDisplayDate(items["prddate"], DateUtil.eLanguage.EN);
            model._aRollno = items["rollno"].ToString();
            model._aLenght = items["length"].ToString();
            model._aWidgth = items["width"].ToString();
            model._aExpired = DateUtil.ToDisplayDate(items["expired"], DateUtil.eLanguage.EN);
            model._a50ds = items["ds50"].ToString();
            model._a50os = items["os50"].ToString();
            model._a50wd = items["wd50"].ToString();
            model._a100ds = items["ds100"].ToString();
            model._a100os = items["os100"].ToString();
            model._a100wd = items["wd100"].ToString();
            model._a200ds = items["ds200"].ToString();
            model._a200os = items["os200"].ToString();
            model._a200wd = items["wd200"].ToString();
            model._a300ds = items["ds300"].ToString();
            model._a300os = items["os300"].ToString();
            model._a300wd = items["wd300"].ToString();
            model._a400ds = items["ds400"].ToString();
            model._a400ms = items["ms400"].ToString();
            model._a400os = items["os400"].ToString();
            model._a400wd = items["wd400"].ToString();
            model._aStdUsed = items["stdused"].ToString();
            model._aEmpname = items["empname"].ToString();
            model._aFindat = DateUtil.ToDisplayDateTime(items["findat"], DateUtil.eLanguage.EN);
            model._aFinflag = items["finflag"].ToString();
            model._aShift=items["shift"].ToString();
            model._aUpdate = DateUtil.ToDisplayDateTime(items["updat"], DateUtil.eLanguage.EN);
            list.Add(model);
         }
         return list;
      }

      public DataTable GET_ACT_FORTOP_DT(string top = "", string prdF = "", string prdT = "")
      {
          string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          SqlConnection cnn = new SqlConnection(ConnectionString);
          StringBuilder str = new StringBuilder();

          str.AppendLine("  select * from ");
          str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.RptHeadNTail p ");
          string where = "";
          if ((StrUtil.IsNotEmpty(prdF) && StrUtil.IsNotEmpty(prdT)))
          {
              where = " where plandt between convert(datetime,@dtF,120) and convert(datetime,@dtT,120) ";
          }
          else if (StrUtil.IsNotEmpty(prdF))
          {
              where = " where plandt=convert(datetime,@dtF,120) ";
          }

          SqlCommand cmd = new SqlCommand(str.ToString() + where + " ORDER BY TOPNO,PLANDT,convert(int,ITEMNO),convert(int,rollno) ", cnn);
          cmd.Connection.Open();
          cmd.CommandType = CommandType.Text;

          if (StrUtil.IsNotEmpty(prdF))
          {
              cmd.Parameters.Add("@dtF", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdF, WinNemoLib.DateUtil.eLanguage.EN);
          }

          if (StrUtil.IsNotEmpty(prdT))
          {
              cmd.Parameters.Add("@dtT", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdT, WinNemoLib.DateUtil.eLanguage.EN);
          }
          SqlDataAdapter my = new SqlDataAdapter();
          DataSet ds = new DataSet();
          my.SelectCommand = cmd;
          my.Fill(ds);

          List<Report_DS> list = new List<Report_DS>();
          if (ds.Tables[0].Rows.Count <= 0)
          {
              cmd.Connection.Close();
              return null;
          }

          return ds.Tables[0];
      }

      public List<Report_DS> GET_COMPOUND_CH(string prdF = "", string prdT = "")
      {
          string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          SqlConnection cnn = new SqlConnection(ConnectionString);
          StringBuilder str = new StringBuilder();

          str.AppendLine(" select * from ");
          str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORD_CMPCHG ");
          string where = "";
          if ((StrUtil.IsNotEmpty(prdF) && StrUtil.IsNotEmpty(prdT)))
          {
              where = " where plandt between convert(datetime,@dtF,120) and convert(datetime,@dtT,120) ";
          }
          else if (StrUtil.IsNotEmpty(prdF))
          {
              where = " where plandt=convert(datetime,@dtF,120) ";
          }

          SqlCommand cmd = new SqlCommand(str.ToString()+where+" order by plandt,convert(int,itemno),convert(int,rollno) " , cnn);
          cmd.Connection.Open();
          cmd.CommandType = CommandType.Text;

          if (StrUtil.IsNotEmpty(prdF))
          {
              cmd.Parameters.Add("@dtF", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdF, WinNemoLib.DateUtil.eLanguage.EN);
          }

          if (StrUtil.IsNotEmpty(prdT))
          {
              cmd.Parameters.Add("@dtT", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdT, WinNemoLib.DateUtil.eLanguage.EN);
          }

          SqlDataAdapter my = new SqlDataAdapter();
          DataSet ds = new DataSet();
          my.SelectCommand = cmd;
          my.Fill(ds);

          List<Report_DS> list = new List<Report_DS>();
          if (ds.Tables[0].Rows.Count <= 0)
          {
              cmd.Connection.Close();
              return list;
          }
          for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
          {
              DataRow items = ds.Tables[0].Rows[cuIndex];
              Report_DS model = new Report_DS();
              model._pTopno = items["topno"].ToString();
              model._pPlandt = DateUtil.ToDisplayDate(items["plandt"],DateUtil.eLanguage.EN);
              model._pItemno = items["itemno"].ToString();
              model._aRollno = items["rollno"].ToString();
              model._pCompcd = items["Compcd"].ToString();
              model._pComplot = items["Complot"].ToString();
              model._pComppllt = items["Comppllt"].ToString();
              model._pCompexp = DateUtil.ToDisplayDate(items["Compexp"], DateUtil.eLanguage.EN);
              model._pCompw = items["compw"].ToString();
              model._pEmpname = items["Cauthemp"].ToString();
              model._pCreatxt = items["cshtxt"].ToString();
              model._pCshtxt = items["cshtxt"].ToString();
              list.Add(model);
          }
          
          return list;
      }


      public DataTable GET_REPORT(string prdDTF,string prdDTT)
      {
          string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          SqlConnection cnn = new SqlConnection(ConnectionString);
          StringBuilder str = new StringBuilder();
          string where = " and prddate=convert(datetime,@dtF,120) ";
          if ((StrUtil.IsNotEmpty(prdDTF) && StrUtil.IsNotEmpty(prdDTT))){
              where = " and prddate between convert(datetime,@dtF,120) and convert(datetime,@dtT,120) ";
          }


          str.AppendLine("  select  a.* ");        
            //str.AppendLine(" ,case when c.compused is null then 0 else 1 end compused");
            str.AppendLine(" from (");
            str.AppendLine(" select  p.topno,prddate,p.plandt,p.topcrdcd,p.Tcrdcd,p.manufc,p.length,a.length alength,stdused ");
            str.AppendLine(" ,p.Tcrdlot,p.itemno,rollno,compcd,complot,Comppllt,compexp,p.compw  from  " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN p ");
            str.AppendLine(" LEFT JOIN  " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT a on p.[topno]=a.[topno] and p.plandt=a.plandt and p.itemno=a.itemno and p.topcrdcd=a.topcrdcd ");
            str.AppendLine(" where Skpflag is null and actflag='X' " + where );
            str.AppendLine(" union select c.topno,'',c.plandt,'','','','','','' ");
            str.AppendLine(" ,'',c.itemno,c.rollno,c.compcd,c.complot,c.Comppllt,c.compexp,'' from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORD_CMPCHG c");
            str.AppendLine("INNER join " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT a on c.[topno]=a.[topno] and c.plandt=a.plandt and c.itemno=a.itemno and c.rollno=a.rollno ");
            str.AppendLine("INNER join " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN p");
            str.AppendLine("on p.[topno]=a.[topno] and p.plandt=a.plandt and p.itemno=a.itemno and p.topcrdcd=a.topcrdcd ");

            str.AppendLine("where Skpflag is null and actflag='X' and stdused is not null "+ where+" )a");
            //str.AppendLine(" LEFT JOIN");
            //str.AppendLine(" (");
            //str.AppendLine(" select topno,compdate,compcd,left(complot,6)complots,sum(compused) compused");
            //str.AppendLine(" from  " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOP_COMPUSED ");
            //str.AppendLine(" group by topno,compdate,compcd,left(complot,6)");
            //str.AppendLine(" )c on a.topno=c.topno and a.plandt=c.compdate ");
            //str.AppendLine(" and a.complot=complots");

          SqlCommand cmd = new SqlCommand(str.ToString() + " order by a.plandt,a.itemno,a.rollno,topcrdcd desc ", cnn);
          cmd.Connection.Open();
          cmd.CommandType = CommandType.Text;

          if (StrUtil.IsNotEmpty(prdDTF))
          {
              cmd.Parameters.Add("@dtF", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdDTF, WinNemoLib.DateUtil.eLanguage.EN);
          }

          if (StrUtil.IsNotEmpty(prdDTT))
          {
              cmd.Parameters.Add("@dtT", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdDTT, WinNemoLib.DateUtil.eLanguage.EN);
          }
          SqlDataAdapter my = new SqlDataAdapter();
          DataSet ds = new DataSet();
          my.SelectCommand = cmd;
          my.Fill(ds);

          if (ds.Tables[0].Rows.Count <= 0)
          {
              cmd.Connection.Close();
              return null;
          }

          return ds.Tables[0];
      }

      public DataTable GET_COMP_USE(string comdt, string compcd,string lot)
      {
          string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          SqlConnection cnn = new SqlConnection(ConnectionString);
          StringBuilder str = new StringBuilder();
          str.AppendLine(" select topno,compdate,compcd,left(complot,6)complots,right(complot,len(complot)-6) Comppllt,sum(compused) compused ");

          str.AppendLine(" from  " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOP_COMPUSED where compdate=convert(datetime,@dtF,120) and compcd='" + compcd +"'");
          str.AppendLine(" and left(complot,6)='"+lot+"'");
          str.AppendLine(" group by topno,compdate,compcd,left(complot,6),right(complot,len(complot)-6) ");
          

          SqlCommand cmd = new SqlCommand(str.ToString() , cnn);
          cmd.Connection.Open();
          cmd.CommandType = CommandType.Text;

          cmd.Parameters.Add("@dtF", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(comdt, WinNemoLib.DateUtil.eLanguage.EN);

          SqlDataAdapter my = new SqlDataAdapter();
          DataSet ds = new DataSet();
          my.SelectCommand = cmd;
          my.Fill(ds);

          if (ds.Tables[0].Rows.Count <= 0)
          {
              cmd.Connection.Close();
              return null;
          }

          return ds.Tables[0];
      }

   }
}